package com.runda.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;

import com.bbjob.dto.Result;
import com.bbjob.mapper.ExamInfoMapper;
import com.bbjob.mapper.ExamineeInfoMapper;
import com.bbjob.mapper.SysDictionaryMapper;
import com.bbjob.mapper.SysOrganizationMapper;
import com.bbjob.mapper.SysOrganizationStuMapper;
import com.bbjob.model.ExamInfo;
import com.bbjob.model.ExamineeInfo;
import com.bbjob.model.SysOrganization;
import com.bbjob.model.SysOrganizationStu;
import com.bbjob.util.service.impl.BaseService;
import com.github.pagehelper.PageHelper;
import com.runda.query.QueryExamInfo;
import com.runda.service.IExamInfoService;

@Service
public class ExamInfoServiceImpl extends BaseService<ExamInfo> implements
		IExamInfoService {

	@Resource
	private SysOrganizationMapper sysOrganizationMapper;

	@Resource
	private ExamInfoMapper examInfoMapper;

	@Resource
	private ExamineeInfoMapper examineeInfoMapper;
	
	@Resource
	private SysOrganizationStuMapper sysOrganizationStuMapper;
	
	@Resource
	private SysDictionaryMapper sysDictionaryMapper;
	
	@Value("${delivery.exam.file.path}")
	private String deliveryExamFileDir;

	@Override
	public List<ExamInfo> selectByParams(QueryExamInfo exam) {

		if (exam.getUniquecode() != null && !"".equals(exam.getUniquecode())) {
			Example organExample = new Example(SysOrganization.class);
			organExample.createCriteria().andEqualTo("uniqueCode",
					exam.getUniquecode());
			List<SysOrganization> organs = sysOrganizationMapper
					.selectByExample(organExample);

			if (organs.size() > 0) {
				exam.setOrganId(organs.get(0).getId());
			}
		}

		PageHelper.startPage(exam.getPageNum(), exam.getPageSize());

		Example example = new Example(ExamInfo.class);
		Criteria c = example.createCriteria();

		if (exam.getEname() != null && !"".equals(exam.getEname())) {
			c.andLike("eName", "%" + exam.getEname() + "%");
		}

		if (exam.getOrganId() != null) {
			c.andEqualTo("organId", exam.getOrganId());
		}
		
		if (exam.getStatus() != null && exam.getStatus() == 4) {
			c.andLessThanOrEqualTo("eStarttime", new Date()).andGreaterThanOrEqualTo("eEndtime", new Date());
		}
		
		if (exam.getStatus() != null && exam.getStatus() == 5) {
			c.andGreaterThan("eStarttime", new Date());
		}
		
		example.setOrderByClause(" create_time desc ");

		List<ExamInfo> list = examInfoMapper.selectByExample(example);
		
		for (ExamInfo item : list) {
			Example examineeInfoExample = new Example(ExamineeInfo.class);
			examineeInfoExample.createCriteria().andEqualTo("eId", item.getId()).andEqualTo("organId", exam.getOrganId());
			
			Integer testNum = examineeInfoMapper.selectCountByExample(examineeInfoExample);
			item.setTestNum(testNum);
		
			item.setServerTime(new Date());
		}

		return list;
	}

	@Override
	public ExamInfo selectByPrimaryKey(Integer id) {
		ExamInfo exam = examInfoMapper.selectByPrimaryKey(id);
		if (exam != null) {
			exam.setServerTime(new Date());
		}

		return exam;
	}

	@Override
	public void deleteAll(Integer id) {
		
		Example example = new Example(ExamineeInfo.class);
		example.createCriteria().andEqualTo("eId", id);
		
		examineeInfoMapper.deleteByExample(example);
		
		examInfoMapper.deleteByPrimaryKey(id);
	}

	@Override
	public Map<String, Object> importExaminee(MultipartFile multipartFile,
			HttpServletRequest request, Integer examInfoId) throws Exception {
		
		request.setCharacterEncoding("UTF-8");
		
		Map<String, Object> returnMap = new HashMap<String, Object>();
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
		String uniquecode = "";
		String filename = "XXX";
		String eId = "";
		if (request.getParameter("uniquecode") != null && !"".equals(request.getParameter("uniquecode"))) {
			uniquecode = request.getParameter("uniquecode");
		} else {
			returnMap.put("code", 8106);
			returnMap.put("message", "学校唯一编码为空");
			returnMap.put("data", null);
			return returnMap;
		}
		if (request.getParameter("filename") != null && !"".equals(request.getParameter("filename"))) {
			filename = request.getParameter("filename");
		}
		
		if (request.getParameter("eId") != null && !"".equals(request.getParameter("eId"))) {
			eId = request.getParameter("eId");
		} else {
			eId = examInfoId != null ? examInfoId.toString() : "";
		}
		
		String saveDirectory = deliveryExamFileDir + uniquecode + "/" + sdf.format(new Date()) + "/" + filename;
		
		InputStream is = null;
		
		try {
			File path = new File(saveDirectory);
			
			if (!path.getParentFile().exists()) {
				path.getParentFile().mkdirs();
				path.createNewFile();
			}
			
			multipartFile.transferTo(path);
			
			// 文件上传完毕，准备解析入库
			is = new FileInputStream(path);
			Workbook wb = null;
			if (excelVersion(path.getName())) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = new HSSFWorkbook(is);
			}
			
			Result res = analysisExcel(wb, uniquecode, eId);
			
			returnMap.put("code", 200);
			returnMap.put("message", res.getMessage());
			returnMap.put("data", null);
			
			// 考生信息入库后分配试卷
			allocatePaper(uniquecode, eId);
			
			return returnMap;
		} catch(Exception e) {
			e.printStackTrace();
			returnMap.put("code", 8106);
			returnMap.put("message", "导入失败，请查看错误信息");
			returnMap.put("data", null);
			return returnMap;
		} finally {
			if (is != null) {
				is.close();
			}
		}
	}
	
	public void allocatePaper(String uniquecode, String eId) throws Exception {
		Example organExample = new Example(SysOrganization.class);
		organExample.createCriteria().andEqualTo("uniqueCode", uniquecode);
		List<SysOrganization> organs = sysOrganizationMapper.selectByExample(organExample);
		
		if (organs.size() == 0) {
			// 无法根据uniquecode查询到该学校
			throw new Exception("无法获取学校信息");
		}
		
		Integer organId = organs.get(0).getId();
		Example examineeExample = new Example(ExamineeInfo.class);
		examineeExample.createCriteria().andEqualTo("organId", organId).andEqualTo("eId", eId);
		examineeExample.setOrderByClause(" e_no ASC ");
		
		List<ExamineeInfo> infos = examineeInfoMapper.selectByExample(examineeExample);
		
		ExamInfo examInfo = examInfoMapper.selectByPrimaryKey(Integer.valueOf(eId));
		if (examInfo != null) {
			String[] qIds = examInfo.getqId().split(",");
			for (int i = 0; i < infos.size(); i++) {
				ExamineeInfo info = infos.get(i);
				info.setField2(qIds[(i % qIds.length)]);
				examineeInfoMapper.updateByPrimaryKeySelective(info);
			}
		}
	}

	private Result analysisExcel(Workbook wb, String uniquecode, String eId) throws Exception {
		
		Example organExample = new Example(SysOrganization.class);
		organExample.createCriteria().andEqualTo("uniqueCode", uniquecode);
		List<SysOrganization> organs = sysOrganizationMapper.selectByExample(organExample);
		ExamInfo examInfoCurr = examInfoMapper.selectByPrimaryKey(Integer.valueOf(eId));
		if (organs.size() == 0) {
			// 无法根据uniquecode查询到该学校
			throw new Exception("无法获取学校信息");
		}
		
		// 获取sheet页数
		int sheets = wb.getNumberOfSheets();
		
		List<String> failSno = new ArrayList<String>();
		List<String> failAll = new ArrayList<String>();
		for (int i = 0; i < sheets; i++) {
			// 得到第i个sheet
			Sheet sheet = wb.getSheetAt(i);
			// 得到当前sheet行数
			int totalRows = sheet.getPhysicalNumberOfRows();
			// 得到当前sheet的有效列数
			int totalCells = 0;
			if (totalRows > 1 && sheet.getRow(0) != null) {
				totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			
			for (int rowIndex = 1; rowIndex < totalRows; rowIndex++) {
				Row row = sheet.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				
				SysOrganizationStu stu = new SysOrganizationStu();
				ExamineeInfo info = new ExamineeInfo();
				stu.setOrganSchool(organs.get(0).getId());
				stu.setSchoolName(organs.get(0).getName());
				info.setOrganId(stu.getOrganSchool());
				info.seteId(Integer.valueOf(eId));
				
				// 用于判断是否是空行 -- 依据是考号列是否为null
				int flag = 0;
				
				for (int colIndex = 0; colIndex < totalCells; colIndex++) {
					Cell cell = row.getCell(colIndex);
					
					if (cell == null) {
						continue;
					}
					
					switch (colIndex) {
					case 0:
						stu.setDepartName(cell.getStringCellValue());
						break;
					case 1:
						stu.setGradeName(cell.getStringCellValue());
						break;
					case 2:
						stu.setClassName(cell.getStringCellValue());
						break;
					case 3:
						stu.setSname(cell.getStringCellValue());
						info.setField1(cell.getStringCellValue());
						break;
					case 4:
						stu.setBirthday(cell.getDateCellValue());
						break;
					case 5:
						stu.setSexName(cell.getStringCellValue());
						break;
					case 6:
						stu.setSno(cell.getStringCellValue());
						info.setsNo(cell.getStringCellValue());
						flag = 1;
						break;
					case 7:
						stu.setIdentityNumber(cell.getStringCellValue());
						break;
					case 8:
						info.seteNo(cell.getStringCellValue());
						break;
					}
				}
				
				// 考号列为null 空行， 跳过
				if (flag == 0) {
					continue;
				}
				
				// 查询是当前学校下是否存在系名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganSchool()).andEqualTo("name", stu.getDepartName());
				List<SysOrganization> departs = sysOrganizationMapper.selectByExample(organExample);
				if (departs.size() == 0) {
					SysOrganization depart = new SysOrganization();
					depart.setPid(stu.getOrganSchool());
					depart.setName(stu.getDepartName());
					depart.setLevel(2);
					depart.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(depart);
					stu.setOrganDepart(depart.getId());
				} else {
					stu.setOrganDepart(departs.get(0).getId());
				}
				
				// 查询当前系下是否存在年级名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganDepart()).andEqualTo("name", stu.getGradeName());
				List<SysOrganization> grades = sysOrganizationMapper.selectByExample(organExample);
				if (grades.size() == 0) {
					SysOrganization grade = new SysOrganization();
					grade.setPid(stu.getOrganDepart());
					grade.setName(stu.getGradeName());
					grade.setLevel(3);
					grade.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(grade);
					stu.setOrganGrade(grade.getId());
				} else {
					stu.setOrganGrade(grades.get(0).getId());
				}
				
				// 查询当前年级下是否存在班级名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganGrade()).andEqualTo("name", stu.getClassName());
				List<SysOrganization> classes = sysOrganizationMapper.selectByExample(organExample);
				if (classes.size() == 0) {
					SysOrganization clas = new SysOrganization();
					clas.setPid(stu.getOrganGrade());
					clas.setName(stu.getClassName());
					clas.setLevel(4);
					clas.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(clas);
					stu.setOrganClass(clas.getId());
				} else {
					stu.setOrganClass(classes.get(0).getId());
				}
				
				if ("男".equals(stu.getSexName())) {
					stu.setSex(1);
				} else if ("女".equals(stu.getSexName())) {
					stu.setSex(2);
				} else {
					stu.setSex(0);
				}
				
				// 信息处理完准备存库
				Example stuExample = new Example(SysOrganizationStu.class);
				stuExample.createCriteria().andEqualTo("sno", stu.getSno()).andEqualTo("organSchool", stu.getOrganSchool());
				List<SysOrganizationStu> stus = sysOrganizationStuMapper.selectByExample(stuExample);
				if (stus.size() > 0) {
					// stu.setId(stus.get(0).getId());
					// sysOrganizationStuMapper.updateByPrimaryKey(stu);
				} else {
					sysOrganizationStuMapper.insertSelective(stu);
				}
				
				Example infoExample = new Example(ExamineeInfo.class);
				
				infoExample.createCriteria().andEqualTo("eId", info.geteId()).andEqualTo("organId", info.getOrganId()).andEqualTo("sNo", info.getsNo()).andEqualTo("eNo", info.geteNo());
				List<ExamineeInfo> examineeInfoSno = examineeInfoMapper.selectByExample(infoExample);
				if (examineeInfoSno.size() > 0) {
					// 重复数据
					continue; // 跳过该条 继续保存
				}
				
				infoExample.clear();
				infoExample.createCriteria().andEqualTo("eId", info.geteId()).andEqualTo("organId", info.getOrganId()).andEqualTo("eNo", info.geteNo());
				List<ExamineeInfo> examineeInfoEno = examineeInfoMapper.selectByExample(infoExample);
				if (examineeInfoEno.size() > 0) {
					// return new Result(8106, "新增失败，考号" + info.geteNo() + "已在此次考试中注册过");
					failSno.add(info.getsNo());
					continue; // 跳过该条 继续保存
				}
				
				infoExample.clear();
				infoExample.createCriteria().andEqualTo("sNo", info.getsNo()).andEqualTo("field1", info.getField1()).andEqualTo("organId", info.getOrganId()).andEqualTo("eNo", info.geteNo());
				List<ExamineeInfo> examineeInfoAll = examineeInfoMapper.selectByExample(infoExample);
				int isExistExam = 0;
				for (ExamineeInfo infoAll : examineeInfoAll) {
					ExamInfo examInfoHis = examInfoMapper.selectByPrimaryKey(infoAll.geteId());
					Long hisEndTime = examInfoHis.geteEndtime().getTime();
					Long currStartTime = examInfoCurr.geteStarttime().getTime();
					if (hisEndTime > currStartTime) {
						failAll.add(info.getsNo());
						isExistExam = 1;
						break;
					}
				}
				if (isExistExam == 1) {
					continue;
				}
				
/*				infoExample.clear();
				infoExample.createCriteria().andEqualTo("organId", info.getOrganId()).andEqualTo("eNo", info.geteNo()).andEqualTo("sNo", info.getsNo());
				List<ExamineeInfo> examineeInfoSno = examineeInfoMapper.selectByExample(infoExample);
				if (examineeInfoSno.size() > 0) {
					return new Result(8106, "新增失败，学号" + info.getsNo() +"和考号" + info.geteNo() + "已在其他考试中使用过");
				}*/
				
				infoExample.clear();
				infoExample.createCriteria().andEqualTo("eId", eId).andEqualTo("organId", stu.getOrganSchool()).andEqualTo("sNo", stu.getSno());
				List<ExamineeInfo> infos = examineeInfoMapper.selectByExample(infoExample);
				if (infos.size() > 0) {
					info.setId(infos.get(0).getId());
					info.setCreateTime(infos.get(0).getCreateTime());
					info.setUpdateTime(new Date());
					examineeInfoMapper.updateByPrimaryKey(info);
				} else {
					info.setCreateTime(new Date());
					info.setUpdateTime(new Date());
					examineeInfoMapper.insertSelective(info);
				}
			}
		}
		
		String msgTip = "考生信息导入成功";
		if (failSno.size() > 0) {
			msgTip = "学号：" + failSno.toString() + "使用的考号在这次考试中已注册，请修改";
			if (failAll.size() > 0) {
				msgTip += "；学号：" + failAll.toString() +"的考生有一场未结束的考试，无法参加此场考试";
			}
		} else {
			if (failAll.size() > 0) {
				msgTip = "学号：" + failAll.toString() +"的考生有一场未结束的考试，无法参加此场考试";
			}
		}
		
		return new Result(200, msgTip);
	}

	private boolean excelVersion(String name) {
		
		return name.matches("^.+\\.(?i)(xlsx)$");
	}

	@Override
	public List<ExamineeInfo> synchronizeExamineeByExam(ExamInfo exam) {
		
		if (exam.getUniquecode() != null && !"".equals(exam.getUniquecode())) {
			Example organExample = new Example(SysOrganization.class);
			organExample.createCriteria().andEqualTo("uniqueCode", exam.getUniquecode());
			List<SysOrganization> organs = sysOrganizationMapper.selectByExample(organExample);

			if (organs.size() > 0) {
				exam.setOrganId(organs.get(0).getId());
			
				Example examExample = new Example(ExamineeInfo.class);
				examExample.createCriteria().andEqualTo("eId", exam.getId()).andEqualTo("organId", exam.getOrganId());
				
				List<ExamineeInfo> examinees = examineeInfoMapper.selectByExample(examExample);

				return examinees;
			}
		}
		
		return null;
	}

}
